package com.source.lightfowl.basedata.dao;

import com.source.lightfowl.VO.ToMarketCurveVO;
import com.source.lightfowl.basedata.entity.ToMarket;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigInteger;
import java.util.*;

/**
 * @Author: cxy
 * @Description: 去向市场信息持久层接口
 */

public interface ToMarketDao extends JpaRepository<ToMarket,String>,JpaSpecificationExecutor<ToMarket>{

    @Query("select distinct city from ToMarket t where t.state = (?1) and t.province = (?2)")
    List<String> findCity(Integer state, String province);

    @Query("select distinct district from ToMarket t where t.state = (?1) and t.province = (?2) and t.city = (?3)")
    List<String> findDistrict(Integer state, String province, String city);

    @Query("from ToMarket t where t.state = (?1) and t.province = (?2) and t.city = (?3) and t.district = (?4)")
    List<ToMarket> findMarket(Integer state, String province, String city, String district);

    @Query("from ToMarket t where t.state = (?1) and t.name LIKE ('%'||?2||'%')")
    List<ToMarket> findByNameLike(Integer state, String name);

    @Query("from ToMarket t where t.state = (?1) and t.stallName LIKE ('%'||?2||'%')")
    List<ToMarket> findByStallNameLike(Integer state, String stallName);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "update to_market set state = (?1) where id in (?2)")
    void changeBatch(Integer state, ArrayList<String> ids);

//    @Query(value = "SELECT DATE_FORMAT(t.createDate,'%Y-%m') createMonth, COUNT(DISTINCT t.stallName) total\n" +
//            "FROM to_market t\n" +
//            "WHERE t.createDate >= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 5 MONTH),'%Y-%m') \n" +
//            "AND t.state = 1" +
//            "GROUP BY createMonth \n" +
//            "ORDER BY createMonth", nativeQuery = true)
//    List<Map<String,Integer>> stallsNumber();

    @Query(nativeQuery = true,
    value = "SELECT createMonth,@num \\:= @num + num AS 'total'\n" +
            "FROM \n" +
            "(SELECT a.createMonth createMonth, IFNULL(b.num, 0) num FROM (\n" +
            "SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `createMonth` UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `createMonth` UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `createMonth` UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `createMonth` UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `createMonth` UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `createMonth`               \n" +
            ") a LEFT JOIN\n" +
            "(SELECT DATE_FORMAT(t.createDate, '%Y-%m') AS createMonth,COUNT(t.stallName) AS num\n" +
            "\tFROM to_market t\n" +
            "\tWHERE t.createDate >= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 5 MONTH),'%Y-%m') \n" +
            "\tAND t.id IN (?1)\n" +
            "\tAND t.state = (?2)" +
            "\tGROUP BY createMonth ) b\n" +
            "  ON a.createMonth=b.createMonth ) AS temp, \n" +
            "\t(SELECT @num \\:= (SELECT COUNT( t.stallName) FROM to_market t WHERE t.createDate < DATE_SUB(NOW(),INTERVAL 6 MONTH))) AS T1\n" +
            "ORDER BY createMonth ;")
    List<Map<Object, Object>> stallsCount(List<String> idList, Integer state);

    Page<ToMarket> findByState(int state, Pageable pageable);

    List<ToMarket> findByState(int state);

    Optional<ToMarket> findByIdAndStateNot(String id, int state);

    @Query(nativeQuery = true,
    value = "select name from to_market where id = ?1")
    String findNameById(String id);

}
